﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Text.RegularExpressions;
using System.Data;
using ZhiAo.WebLib.Data;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using ZhiAo.Entities;
using ZhiAo.Management.Controls;
using ZhiAo.CommonLibrary;

namespace ZhiAo.Management
{
    public class TabDataEdit
    {
        // 获取单表数据结构
        private static DataTable GetDataStruct(string TableName)
        {
            string sql = "";
            sql += " SELECT ";
            sql += " SYSOBJECTS.NAME AS TABLENAME, ";
            sql += " SYSCOLUMNS.NAME AS COLUMN_NAME, ";
            sql += " SYSTYPES.NAME AS DATA_TYPE, ";
            sql += " SYSCOLUMNS.LENGTH AS LEN, ";
            sql += " SYS.EXTENDED_PROPERTIES.[VALUE] AS COMMENTS, ";
            sql += " SYSCOMMENTS.TEXT AS [默认值], ";
            sql += " SYSCOLUMNS.ISNULLABLE  AS NULLABLE, ";
            sql += " ISNULL(COLUMNPROPERTY(SYSCOLUMNS.ID, SYSCOLUMNS.NAME, 'SCALE'), 0) AS [小数位数], ";
            sql += " COLUMNPROPERTY(SYSCOLUMNS.ID, SYSCOLUMNS.NAME, 'ISIDENTITY') AS [自增标识] , ";
            sql += " CASE WHEN EXISTS (SELECT 1 FROM SYSOBJECTS WHERE XTYPE = 'PK' AND NAME IN ";
            sql += " (SELECT NAME ";
            sql += " FROM SYSINDEXES ";
            sql += " WHERE INDID IN ";
            sql += " (SELECT INDID ";
            sql += " FROM SYSINDEXKEYS ";
            sql += " WHERE ID = SYSCOLUMNS.ID AND COLID = SYSCOLUMNS.COLID))) ";
            sql += " THEN '1' ELSE '' END AS [KEY] ";
            sql += " FROM SYSCOLUMNS ";
            sql += " INNER JOIN SYSTYPES ";
            sql += " ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE ";
            sql += " LEFT JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID ";
            sql += " LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES ON ";
            sql += " ( SYS.EXTENDED_PROPERTIES.MINOR_ID = SYSCOLUMNS.COLID ";
            sql += " AND SYS.EXTENDED_PROPERTIES.MAJOR_ID = SYSCOLUMNS.ID) ";
            sql += " LEFT OUTER JOIN SYSCOMMENTS ON SYSCOLUMNS.CDEFAULT = SYSCOMMENTS.ID ";
            sql += " WHERE  SYSCOLUMNS.ID IN (SELECT ID FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '{0}')";
            sql = string.Format(sql, TableName);
            DataTable dt = DBHelper.GetDataTable(sql);
            return dt;
        }

        //保存
        public static void SaveData(List<FieldItem> lstFieldItem, HtmlTable layoutControl, string keyValue)
        {
            Control control = null;
            #region 查找并移除Key实体
            //var fieldItem = from item in lstFieldItem where item.Key = true select item;
            //FieldItem keyFieldItem = fieldItem.First(p => p.Key = true);
            FieldItem keyFieldItem = GetPrimaryItem(lstFieldItem);
            control = FindControl(layoutControl, keyFieldItem.ColumnName);
            HiddenField txtHidden = control as HiddenField;
            string KEY_0 = txtHidden.Value;
            string KEY_0Value = keyValue;
            if (KEY_0 == "")//新增时候移除主键
                lstFieldItem.Remove(keyFieldItem);
            #endregion

            string sql = "";
            string value = "";
            string updKeyV = "";
            string insKey = "";
            string insValue = "";
            IControl iControl = null;

            #region  获取value
            foreach (FieldItem field in lstFieldItem)
            {
                control = FindControl(layoutControl, field.ColumnName);
                iControl = ControlFactory.GetInstance(control);
                if (iControl == null)
                    continue;
                CheckSave(iControl, field);
                value = iControl.Value;
                switch (field.DataType.ToUpper())
                {
                    case "VARCHAR2":
                        value = "'" + value + "'";
                        break;
                    case "CHAR":
                        value = "'" + value + "'";
                        break;
                    case "DATE":
                        value = "CONVERT(varchar, '" + value + "', 120 )";
                        break;
                    case "FLOAT":
                        value = "'" + value + "'";
                        break;
                    default:
                        break;
                }

                if (KEY_0 == "")//新增模式
                {
                    insKey += "," + field.ColumnName;
                    insValue += "," + value;
                }
                else
                {
                    updKeyV += "," + field.ColumnName + "=" + value;
                }
            }
            #endregion

            if (KEY_0 == "")//新增模式
            {
                sql = "INSERT INTO {0} ({1},{2}) VALUES ({3},{4})";
                sql = string.Format(sql, keyFieldItem.TableName, keyFieldItem.ColumnName, insKey.Substring(1), KEY_0Value, insValue.Substring(1));
                txtHidden.Value = KEY_0Value;
            }
            else
            {
                sql = " UPDATE {0} SET {1} WHERE  {2} = '{3}'";
                sql = string.Format(sql, keyFieldItem.TableName, updKeyV.Substring(1), keyFieldItem.ColumnName, KEY_0);
            }
            DBHelper.ExecuteNoneQuery(sql);
        }

        //反显数据
        public static void BuildEdit(List<FieldItem> lstFieldItem, HtmlTable layoutControl)
        {
            string Key = PageHelper.Request("key");
            FieldItem fieldItem = GetPrimaryItem(lstFieldItem);
            string sql = "SELECT * FROM {0} WHERE {1} = '{2}'";
            sql = string.Format(sql, fieldItem.TableName, fieldItem.ColumnName, Key);
            DataTable dtEditData = DBHelper.GetDataTable(sql);
            Control control = null;
            IControl iControl = null;
            foreach (FieldItem field in lstFieldItem)
            {
                control = FindControl(layoutControl, field.ColumnName);
                iControl = ControlFactory.GetInstance(control);
                if (iControl != null && dtEditData.Rows.Count > 0)
                    iControl.Value = dtEditData.Rows[0][field.ColumnName].ToString().Trim();
            }
        }

        //保存前基本校验
        private static void CheckSave(IControl iControl, FieldItem item)
        {
            string cValue = iControl.Value;
            #region 基本验证
            if (!item.NullAble)//非空验证
            {
                if (cValue == "")
                    throw new Exception("请输入" + item.Comments + ":" + item.ColumnName);
                if (cValue.Length > item.Len)
                    throw new Exception("输入文字过多" + item.Comments + ":" + item.ColumnName);
            }
            #endregion
        }

        //获取控件
        private static Control FindControl(HtmlTable layoutControl, string Name)
        {
            Control control = null;
            ArrayList arrPrefix = new ArrayList();
            //待添加
            arrPrefix.Add("txt");
            arrPrefix.Add("rdo");
            arrPrefix.Add("cbo");
            foreach (string item in arrPrefix)
            {
                if (control != null)
                    break;
                control = layoutControl.FindControl(item + Name);
            }
            return control;
        }

        //获取所有字段Item
        public static List<FieldItem> GetListFieldItem(string TableName)
        {
            DataTable dt = GetDataStruct(TableName);
            List<FieldItem> lst = new List<FieldItem>();
            FieldItem Model = null;
            foreach (DataRow dr in dt.Rows)
            {
                Model = new FieldItem();
                Model.ColumnName = dr["COLUMN_NAME"].ToString();
                Model.DataType = dr["DATA_TYPE"].ToString();
                Model.Len = Int32.Parse(dr["LEN"].ToString());
                Model.Comments = dr["COMMENTS"].ToString();
                Model.TableName = dr["TABLENAME"].ToString();
                Model.Key = dr["KEY"].ToString() == "1" ? true : false;
                Model.NullAble = dr["NULLABLE"].ToString() == "1" ? true : false;
                lst.Add(Model);
            }
            return lst;
        }

        //获取主键Item
        private static FieldItem GetPrimaryItem(List<FieldItem> lstFieldItem)
        {
            //var fieldItem = from item in lstFieldItem where item.Key = true select item;
            FieldItem keyFieldItem = lstFieldItem.First(p => p.Key == true);
            return keyFieldItem;
        }
    }
}
